
package ar.com.mej.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

import ar.com.mej.utils.Utils;

/**
 *
 * @author Administrador
 */
public class Connection {
    
    private static java.sql.Connection conn;
    private String driver;
    private String url;
    private String user;
    private String password;
    private Properties props = new Utils().getPropertiesByResource("resource/configuration.properties");

	public String getSchema()
	{
		String schema = props.getProperty("db.name");
		return schema;
		//TODO Mostrar excepcion o algo cuando no este la entrada en el properties
	}
	
    /** Creates a new instance of ConexionAvw */
    public Connection() {
        loadConfiguration();
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            System.out.print("Error " + ex.toString());            
        }
    }
    
    private void loadConfiguration()  {
        Properties p = new Properties();
        try {
            InputStream file =
                this.getClass().getClassLoader().getResourceAsStream("resource/configuration.properties");
            p.load(file);
            this.driver = p.getProperty("driver");
            this.user = p.getProperty("usuario", "");
            this.password = p.getProperty("password", "");
            this.url = p.getProperty("url");
            Locale.setDefault(Locale.ENGLISH);
            
        } catch (IOException ex) {
        	System.out.print("Error " + ex.toString());
        }
    }
    
    public void abrirConexion() throws SQLException {

    	conn = DriverManager.getConnection(url, user, password);
    }
    
    public void cerrarConexion() 
    {    	
    	try 
    	{
	    	conn.close();
    	}
    	catch (SQLException ex) 
    	{
    		System.out.println(ex);
    	}
    }
        
	public void startTransaction() throws SQLException {
        conn.setAutoCommit(false);
    }
    
    public void commitTransaction() throws SQLException {
        conn.commit();
    }
    
    public void rollbackTransaction() throws SQLException {
        conn.rollback();
    }
       
    public ResultSet getResultSet(StringBuilder sql) throws SQLException {
        PreparedStatement pstmt = null;
        pstmt = conn.prepareStatement(sql.toString());
        pstmt.executeQuery();
        return pstmt.getResultSet();
    }

	public  ResultSet getPreparedStatementResultSet(String sql, List boundparams) throws SQLException {
        
        PreparedStatement pstmt = null;
        ResultSet r = null;
        pstmt = conn.prepareStatement(sql);
        setPreParams(pstmt, boundparams);// Seteo los parametros
        r = pstmt.executeQuery();
        return r;
    }
    
	public  ResultSet getPreparedStatementResultSet(StringBuilder sql,List boundparams) throws SQLException{
        return getPreparedStatementResultSet(sql.toString(),boundparams);
    }
    
	public void executePreparedStatement(String sql,List boundparams) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPreParams(pstmt, boundparams);// Seteo los parametros
            pstmt.executeQuery();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    }
    
    @SuppressWarnings("unchecked")
	public  void executePreparedStatement(StringBuilder sql,
        List boundparams) throws SQLException {
        executePreparedStatement(sql.toString(),boundparams); 
    }
    
	private void setPreParams(PreparedStatement ps,List plist)  throws SQLException {
        
        List boundParams = plist;
        if (ps == null || boundParams == null || boundParams.size() == 0)
            return;
        
        BoundParam param;
        
        for (Iterator itr = boundParams.iterator(); itr.hasNext();) {
            param = (BoundParam) itr.next();
            if (param == null)
                continue;
            if (param.sqlType == java.sql.Types.OTHER) {
                ps.setObject(param.index, param.value);
            } else {     
                ps.setObject(param.index, param.value, param.sqlType,  param.scale);
            }
        }
    }    
    
    public Statement createStatement() throws SQLException
    {
    	Statement stmt = conn.createStatement();    	
    	return stmt;
    }
    
    public void closeStatement(Statement statement)
    {
    	try {
			if (statement != null)
				statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }    
}
    

